﻿Imports System.Data.OleDb

Public Class ThoiKhoaBieuDAO
    Dim sDBName As String = "Database\thietkethoikhoabieu.mdb"

    Public Function LayDanhSachThoiKhoaBieu() As List(Of ThoiKhoaBieuDTO)
        Dim str = "SELECT * FROM ThoiKhoaBieu"
        Dim conn = DataProvider.ConnectDB(sDBName)
        Dim cmd As New OleDbCommand(str, conn)
        Dim reader = cmd.ExecuteReader()
        Dim ds As New List(Of ThoiKhoaBieuDTO)

        While reader.Read()
            Dim tkb As New ThoiKhoaBieuDTO
            tkb.MaTKB = reader.GetString(0)
            tkb.Thu = reader.GetString(1)
            tkb.Buoi = reader.GetString(2)
            tkb.MaLop = reader.GetString(3)
            tkb.TenLop = reader.GetString(4)

            ds.Add(tkb)
        End While

        conn.Close()
        Return ds
    End Function

    Public Function ThemThoiKhoaBieu(ByVal tkb As ThoiKhoaBieuDTO) As Integer
        Dim str = String.Format("INSERT INTO ThoiKhoaBieu(MaTKB, Thu, Buoi, MaLop, TenLop) " _
                & "values ('{0}','{1}','{2}','{3}','{4}')", tkb.MaTKB, tkb.Thu, tkb.Buoi, tkb.MaLop, tkb.TenLop)
        Dim conn = DataProvider.ConnectDB(sDBName)
        Dim cmd As New OleDbCommand(str, conn)
        Dim ketqua = -1
        ketqua = cmd.ExecuteNonQuery()
        conn.Close()

        Return ketqua
    End Function

    Public Function XoaThoiKhoaBieu(ByVal tkb As ThoiKhoaBieuDTO) As Integer
        Dim str = "DELETE FROM ThoiKhoaBieu WHERE MaTKB='" & tkb.MaTKB & "'"
        Dim conn = DataProvider.ConnectDB(sDBName)
        Dim cmd As New OleDbCommand(str, conn)
        Dim ketqua = -1
        ketqua = cmd.ExecuteNonQuery()

        conn.Close()
        Return ketqua
    End Function

    Public Function SuaThoiKhoaBieu(ByVal tkb As ThoiKhoaBieuDTO) As Integer
        Dim str = String.Format("UPDATE ThoiKhoaBieu SET Thu='{0}', Buoi='{1}', MaLop='{2}', TenLop='{3}' " _
                & "WHERE MaTKB='{4}'", tkb.Thu, tkb.Buoi, tkb.MaLop, tkb.TenLop, tkb.MaTKB)
        Dim conn = DataProvider.ConnectDB(sDBName)
        Dim cmd As New OleDbCommand(str, conn)
        Dim ketqua = -1
        ketqua = cmd.ExecuteNonQuery()

        conn.Close()
        Return ketqua
    End Function

    Public Function TraCuuTKB(ByVal tkb As ThoiKhoaBieuDTO) As List(Of ThoiKhoaBieuDTO)
        Dim sSelect = String.Format("SELECT * FROM ThoiKhoaBieu WHERE MaTKB LIKE '%{0}%' AND " _
                    & "Thu LIKE '%{1}%' AND Buoi LIKE '%{2}%' ", tkb.MaTKB, tkb.Thu, tkb.Buoi)

        Dim conn = DataProvider.ConnectDB(sDBName)
        Dim cmd As New OleDbCommand(sSelect, conn)
        Dim reader = cmd.ExecuteReader()
        Dim ds As New List(Of ThoiKhoaBieuDTO)

        While reader.Read()
            Dim rtkb As New ThoiKhoaBieuDTO
            rtkb.MaTKB = reader.GetString(0)
            rtkb.Thu = reader.GetString(1)
            rtkb.Buoi = reader.GetString(2)
            rtkb.MaLop = reader.GetString(3)
            rtkb.TenLop = reader.GetString(4)

            ds.Add(rtkb)
        End While

        conn.Close()
        Return ds

    End Function

    Public Function TraCuuTTTKB(ByVal tkb As BaoCaoThoiKhoaBieuDTO) As List(Of BaoCaoThoiKhoaBieuDTO)
        Dim sSelect = String.Format("SELECT * FROM BaoCaoTKB WHERE TenLop LIKE '%{0}%' AND Thu LIKE '%{1}%' " _
                    & " AND Buoi LIKE '%{2}%' AND Tiet LIKE '%{3}%' AND TenMonHoc LIKE '%{4}%' AND TenGV LIKE '%{5}%' ", _
                    tkb.TenLop, tkb.Thu, tkb.Buoi, tkb.Tiet, tkb.TenMonHoc, tkb.TenGV)

        Dim conn = DataProvider.ConnectDB(sDBName)
        Dim cmd As New OleDbCommand(sSelect, conn)
        Dim reader = cmd.ExecuteReader()
        Dim ds As New List(Of BaoCaoThoiKhoaBieuDTO)

        While reader.Read()
            Dim rtkb As New BaoCaoThoiKhoaBieuDTO
            rtkb.TenLop = reader.GetString(0)
            rtkb.Thu = reader.GetString(1)
            rtkb.Buoi = reader.GetString(2)
            rtkb.Tiet = reader.GetString(3)
            rtkb.TenMonHoc = reader.GetString(4)
            rtkb.TenGV = reader.GetString(5)

            ds.Add(rtkb)
        End While

        conn.Close()
        Return ds

    End Function

End Class
